﻿using MySql.Data.MySqlClient;
using System;
using System.Data;
using UnityEngine;

public class MySqlAccess
{

    public static MySqlConnection mySqlConnection;

    static string host = "127.0.0.1";//这里是ip地址

    static string id = "root";//用户名

    static string pwd = "";//密码

    static string database = "game";//要连接的数据库

    public static int gold;//金币数量
    public static int jewel;//钻石数量
    public static int level;//等级数量
    public static int exp;//经验数量
    public static string ids;

    public static void OpenSql()
    {
        try
        {
            string sqlString = string.Format("Server={0};Database={1};User ID ={2};Password ={3};", host, database, id, pwd);

            mySqlConnection = new MySqlConnection(sqlString);

            mySqlConnection.Open();
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
    }

    //-----------------------------------------------增/删/改/查----------------------------------------------------------
    /// <summary>  
    /// 创建表  
    /// </summary>  
    /// <param name="name">表名</param>  
    /// <param name="colName">属性列</param>  
    /// <param name="colType">属性类型</param>  
    /// <returns></returns>  
    public static DataSet CreateTable(string name, string[] colName, string[] colType)
    {
        if (colName.Length != colType.Length)
        {
            throw new Exception("输入不正确：" + "columns.Length != colType.Length");
        }
        string query = "CREATE TABLE  " + name + "(" + colName[0] + " " + colType[0];
        for (int i = 1; i < colName.Length; i++)
        {
            query += "," + colName[i] + " " + colType[i];
        }
        query += ")";
        return QuerySet(query);
    }

    /// <summary>  
    /// 创建具有id自增的表  
    /// </summary>  
    /// <param name="name">表名</param>  
    /// <param name="col">属性列</param>  
    /// <param name="colType">属性列类型</param>  
    /// <returns></returns>  
    public static DataSet CreateTableAutoID(string name, string[] col, string[] colType)
    {
        if (col.Length != colType.Length)
        {
            throw new Exception("columns.Length != colType.Length");
        }
        string query = "CREATE TABLE  " + name + " (" + col[0] + " " + colType[0] + " NOT NULL AUTO_INCREMENT";
        for (int i = 1; i < col.Length; ++i)
        {
            query += ", " + col[i] + " " + colType[i];
        }
        query += ", PRIMARY KEY (" + col[0] + ")" + ")";

        return QuerySet(query);
    }

    /// <summary>  
    /// 插入一条数据，包括所有，不适用自动累加ID。  
    /// </summary>  
    /// <param name="tableName">表名</param>  
    /// <param name="values">插入值</param>  
    /// <returns></returns>  
    public static DataSet InsertInto(string tableName, string[] values)
    {
        string query = "INSERT INTO " + tableName + " VALUES (" + "'" + values[0] + "'";
        for (int i = 1; i < values.Length; ++i)
        {
            query += ", " + "'" + values[i] + "'";
        }
        query += ")";
        return QuerySet(query);
    }

    /// <summary>  
    /// 插入部分ID  
    /// </summary>  
    /// <param name="tableName">表名</param>  
    /// <param name="col">属性列</param>  
    /// <param name="values">属性值</param>  
    /// <returns></returns>  
    public static DataSet InsertInto(string tableName, string[] col, string[] values)
    {
        if (col.Length != values.Length)
        {
            throw new Exception("columns.Length != colType.Length");
        }
        string query = "INSERT INTO " + tableName + " (" + col[0];
        for (int i = 1; i < col.Length; ++i)
        {
            query += ", " + col[i];
        }
        query += ") VALUES (" + "'" + values[0] + "'";
        for (int i = 1; i < values.Length; ++i)
        {
            query += ", " + "'" + values[i] + "'";
        }
        query += ")";
        return QuerySet(query);
    }

    /// <summary>  
    /// 查询表数据 
    /// </summary>  
    /// <param name="tableName">表名</param>  
    /// <param name="items">需要查询的列</param>  
    /// <param name="whereColName">查询的条件列</param>  
    /// <param name="operation">条件操作符</param>  
    /// <param name="value">条件的值</param>  
    /// <returns></returns>  
    public static DataSet Select(string tableName, string[] items, string[] whereColName, string[] operation, string[] value)
    {
        //Debug.Log(whereColName[0]);
        //Debug.Log(value[0]);
        if (whereColName.Length != operation.Length || operation.Length != value.Length)
        {
            throw new Exception("输入不正确：" + "col.Length != operation.Length != values.Length");
        }
        string query = "SELECT " + items[0];
        for (int i = 1; i < items.Length; i++)
        {
            query += "," + items[i];
        }
        query += "  FROM  " + tableName + "  WHERE " + " " + whereColName[0] + operation[0] + " '" + value[0] + "'";
        for (int i = 1; i < whereColName.Length; i++)
        {
            query += " AND " + whereColName[i] + operation[i] + "' " + value[i] + "'";
        }
        return QuerySet(query);
    }

    /// <summary>  
    /// 更新表数据 
    /// </summary>  
    /// <param name="tableName">表名</param>  
    /// <param name="cols">更新列</param>  
    /// <param name="colsvalues">更新的值</param>  
    /// <param name="selectkey">条件：列</param>  
    /// <param name="selectvalue">条件：值</param>  
    /// <returns></returns>  
    public static DataSet UpdateInto(string tableName, string[] cols, string[] colsvalues, string selectkey, string selectvalue)
    {
        string query = "update " + tableName + " set " + cols[0] + " = " + colsvalues[0] + " where " + selectkey + " = " + "'" + selectvalue + "'";
        //Debug.Log(query);
        //Debug.Log(colsvalues[0]);
        //string query = "UPDATE " + tableName + " SET " + cols[0] + " = " + colsvalues[0];
        //for (int i = 1; i < colsvalues.Length; ++i)
        //{
        //    query += ", " + cols[i] + " =" + colsvalues[i];
        //}
        //query += " WHERE " + selectkey + " = " + selectvalue + " ";
        return QuerySet(query);
    }

    /// <summary>  
    /// 删除表数据  
    /// </summary>  
    /// <param name="tableName">表名</param>  
    /// <param name="cols">条件：删除列</param>  
    /// <param name="colsvalues">删除该列属性值所在得行</param>  
    /// <returns></returns>  
    public static DataSet Delete(string tableName, string[] cols, string[] colsvalues)
    {
        string query = "DELETE FROM " + tableName + " WHERE " + cols[0] + " = " + colsvalues[0];
        for (int i = 1; i < colsvalues.Length; ++i)
        {
            query += " or " + cols[i] + " = " + colsvalues[i];
        }
        return QuerySet(query);
    }

   
    //public void Close()
    //{
    //    if (PaiHangBang.mySqlConnection != null)
    //    {
    //        PaiHangBang.mySqlConnection.Close();
    //        PaiHangBang.mySqlConnection.Dispose();
    //        PaiHangBang.mySqlConnection = null;
    //    }
    //}

    /// <summary>    
    /// 执行Sql语句  
    /// </summary>  
    /// <param name="sqlString">sql语句</param>  
    /// <returns></returns>  
    public static DataSet QuerySet(string sqlString)
    {
        if (mySqlConnection.State == ConnectionState.Open)
        {
            DataSet ds = new DataSet();
            try
            {
                MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sqlString, mySqlConnection);
                mySqlDataAdapter.Fill(ds);
            }
            catch (Exception e)
            {
                throw new Exception("SQL:" + sqlString + "/n" + e.Message.ToString());
            }
            finally
            {
            }
            return ds;
        }       
        return null;
    }

    //查询所有（列名）
    public static DataSet SelectAllId()
    {
        string query = "select id from shop";
        return QuerySet(query);
    }
    //查询所有（列名）
    public static DataSet SelectAllGold()
    {
        string query = "select gold from shop";
        return QuerySet(query);
    }

    //--------------------------------------------------------具体查询------------------------------------------------------------
    //查询金币表
    public static int QueryGold()
    {
        //查询
        DataSet ds = MySqlAccess.Select("shop", new string[] { "gold" }, new string[] { "id" }, new string[] { "=" }, new string[] { LoginPanel.Showtetx() });
        if (ds != null)
        {
            DataTable table = ds.Tables[0];
            foreach (DataRow row in table.Rows)//行
            {
                foreach (DataColumn column in table.Columns)//列
                {
                    gold = int.Parse(row[column].ToString());
                }
            }
        }
        return gold;
    }

    //查询钻石表
    public static int QueryJewel()
    {
        //查询
        DataSet ds = MySqlAccess.Select("shop", new string[] { "jewel" }, new string[] { "id" }, new string[] { "=" }, new string[] { LoginPanel.Showtetx() });
        if (ds != null)
        {
            DataTable table = ds.Tables[0];
            foreach (DataRow row in table.Rows)//行
            {
                foreach (DataColumn column in table.Columns)//列
                {
                    jewel = int.Parse(row[column].ToString());
                }
            }
        }
        return jewel;
    }

    //查询等级表
    public static int QueryLevel()
    {
        //查询
        DataSet ds = MySqlAccess.Select("player", new string[] { "level" }, new string[] { "id" }, new string[] { "=" }, new string[] { LoginPanel.Showtetx() });
        if (ds != null)
        {
            DataTable table = ds.Tables[0];
            foreach (DataRow row in table.Rows)//行
            {
                foreach (DataColumn column in table.Columns)//列
                {
                    level = int.Parse(row[column].ToString());
                }
            }
        }
        return level;
    }
    //查询经验表
    public static int QueryExp()
    {
        //查询
        DataSet ds = MySqlAccess.Select("player", new string[] { "exp" }, new string[] { "id" }, new string[] { "=" }, new string[] { LoginPanel.Showtetx() });
        if (ds != null)
        {
            DataTable table = ds.Tables[0];
            foreach (DataRow row in table.Rows)//行
            {
                foreach (DataColumn column in table.Columns)//列
                {
                    exp = int.Parse(row[column].ToString());
                }
            }
        }
        return exp;
    }

    //查询ID表
    public static string QueryIDS(string str)
    {
        //查询
        DataSet ds = MySqlAccess.Select("shop", new string[] { "id" }, new string[] { "gold" }, new string[] { "=" }, new string[] { str });
        if (ds != null)
        {
            DataTable table = ds.Tables[0];
            foreach (DataRow row in table.Rows)//行
            {
                foreach (DataColumn column in table.Columns)//列
                {
                    ids = row[column].ToString();
                }
            }
        }
        return ids;
    }
}